package com.etime.shycourse.dao;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.etime.shycourse.dto.*;
import com.etime.shycourse.pojo.Settlement;
import org.apache.ibatis.annotations.*;
import com.etime.shycourse.pojo.SysUser;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * 财务计算记录dao层
 */
@Repository
public interface SettlementMapper extends BaseMapper<Settlement> {


    // 根据时间段（选择时间段-按照结算时间统计/如果没有填写时间时段，则统计所有记录）统计，在该时间段中的所有的提成结算记录（按照用户分组统计）；
    // 查询条件：时间段，结算类型
    // 统计结果：结算类型，开始时间，结束时间，结算对象id（姓名/名称），总提成金额，已支付总金额，未支付总金额；
    // 统计结果提供导出excel功能。
    // 结果列表中，包含按钮“查询明细”
    @Select({"<script> "+
            "select settlementtype,settlementuserid,sum(settlementamount) totalCommissionMoney," +
            "sum(CASE WHEN settlementpay=1 THEN settlementamount ELSE 0 END) AS paidTotalMoney," +
            "sum(CASE WHEN settlementpay=0 THEN settlementamount ELSE 0 END) AS notPaidTotalMoney," +
            "settlementpatytime,settlementtime,settlementpay" +
            " from settlement s"+
            "<where>"+
            "<if test='settlementType != -1'>"+
            " settlementtype = #{settlementType}"+
            "</if>"+
            // 根据时间段进行筛选
            "<if test='startDate != null'>"+
            " and settlementpatytime &gt; #{startDate}"+
            "</if>"+
            "<if test='endDate != null'>"+
            " and settlementpatytime &lt; #{endDate}"+
            "</if>"+
            "</where>"+
            "<if test='settlementType != -1'>"+
            "group by settlementuserid"+
            "</if>"+
            "<if test='settlementType == -1'>"+
            "group by settlementtype,settlementuserid"+
            "</if>"+
            "</script>"})
    public Page<SettlementDto> selectAllSettlement(String settlementType,String startDate, String endDate,Page<SettlementDto> page);


    // 在按销售/教师/机构统计结果中，点击“查询明细”,进入到统计明细查询；
    // 传递参数：结算类型，开始时间，结束时间，结算对象id（姓名）
    // 查询结果：结算类型，结算对象id（姓名），销售课程（关联订单明细查询），销售金额，提成比例，提成金额，结算时间，是否支付，支付时间。
    // 统计明细查询提供导出到excel功能
    @Select("<script>"+
            "select settlementtype,settlementuserid,c.coursetitle odcoursename," +
            "settlementsale,settlementpoint,settlementamount,settlementtime," +
            "settlementpay,settlementpatytime" +
            " from settlement s" +
            " inner join orderdetail o" +
            " inner join course c" +
            " on s.odid = o.odid" +
            " and o.odcourseid = c.courseid" +
            "<where>"+
            "s.settlementtype = #{settlementtype} and s.settlementuserid = #{settlementuserid}"+
            // 根据时间段进行筛选
            "<if test='startDate != null'>"+
            " and settlementpatytime &gt; #{startDate}"+
            "</if>"+
            "<if test='endDate != null'>"+
            " and settlementpatytime &lt; #{endDate}"+
            "</if>"+
            "</where>"+
            "</script>")
    public Page<SettlementDto> selectSettlementDetail(String settlementtype,String startDate,String endDate,String settlementuserid,Page<SettlementDto> page);


    // 按照课程进行分组统计
    // 查询条件：课程类型（线上课程类型，支招类课程）。查询该分类下所有课程的销售情况。
    // 课程名称，教师，机构，总销售金额，总提成金额，已支付总金额，未支付总金额，销售提成总金额，
    // 销售已支付总金额，销售未支付总金额；教师提成总金额，教师已支付总金额，教师未支付总金额；
    // 机构提成总金额，机构已支付总金额，机构未支付总金额
    // 统计结果提供导出excel功能；
    // 查询结果包含按钮“查询明细”
    @Select("SELECT\n" +
            "course.courseid,course.coursetitle,course.courseteachername,org.orgname courseOrgName,\n" +
            "-- 课程总销售金额\n" +
            "avg(od.odpay) AS totalSellMoney,\n" +
            "-- 总提成金额\n" +
            "sum(settlement.settlementamount) totalCommissionMoney,\n" +
            "-- 已支付总金额\n" +
            "sum(CASE WHEN settlement.settlementpay=1 THEN settlement.settlementamount ELSE 0 END) AS paidTotalMoney,\n" +
            "-- 未支付总金额\n" +
            "sum(CASE WHEN settlement.settlementpay=0 THEN settlement.settlementamount ELSE 0 END) AS notPaidTotalMoney,\n" +
            "-- 销售提成总金额\n" +
            "sum(CASE WHEN settlement.settlementtype = 2 THEN settlement.settlementamount ELSE 0 END) salerCommissionTotalMoney,\n" +
            "-- 销售已支付总金额\n" +
            "sum(CASE WHEN settlement.settlementtype = 2 AND settlement.settlementpay=1 THEN settlement.settlementamount ELSE 0 END) AS salerPaidTotalMoney,\n" +
            "-- 销售未支付总金额\n" +
            "sum(CASE WHEN settlement.settlementtype = 2 AND settlement.settlementpay=0 THEN settlement.settlementamount ELSE 0 END) AS salerNotPaidTotalMoney,\n" +
            "-- 教师提成总金额\n" +
            "sum(CASE WHEN settlement.settlementtype = 3 THEN settlement.settlementamount ELSE 0 END) AS teacherCommissionTotalMoney,\n" +
            "-- 教师已支付总金额\n" +
            "sum(CASE WHEN settlement.settlementtype = 3 AND settlement.settlementpay=1 THEN settlement.settlementamount ELSE 0 END) AS teacherPaidTotalMoney,\n" +
            "-- 教师未支付总金额\n" +
            "sum(CASE WHEN settlement.settlementtype = 3 AND settlement.settlementpay=0 THEN settlement.settlementamount ELSE 0 END) AS teacherNotPaidTotalMoney,\n" +
            "-- 机构提成总金额\n" +
            "sum(CASE WHEN settlement.settlementtype = 1 THEN settlement.settlementamount ELSE 0 END) AS orgComissionTotalMoney,\n" +
            "-- 机构已支付总金额\n" +
            "sum(CASE WHEN settlement.settlementtype = 1 AND settlement.settlementpay=1 THEN settlement.settlementamount ELSE 0 END) AS orgPaidTotalMoney,\n" +
            "-- 机构未支付总金额\n" +
            "sum(CASE WHEN settlement.settlementtype = 1 AND settlement.settlementpay=0 THEN settlement.settlementamount ELSE 0 END) AS orgNotPaidTotalMoney\n" +
            "FROM settlement INNER JOIN orderdetail AS od on settlement.odid = od.odid\n" +
            "INNER JOIN course on od.odcourseid = course.courseid\n" +
            "INNER JOIN organization AS org on course.courseorgid = org.orgid " +
            "${ew.customSqlSegment}" +
            " GROUP BY course.courseid ")
    public Page<CourseSellConditionDto> selectAllCourse(@Param(Constants.WRAPPER) QueryWrapper<String> wrapper,Page<CourseSellConditionDto> page);


    // 在按课程统计中，点击“查询明细”进入到课程销售明细页面
    // 查询结果：课程名称，销售金额，结算时间，结算类型，结算对象，提成比例，提成金额，是否支付，支付时间。
    // 统计结果提供导出到excel功能
    @Select("select coursetitle odcoursename,odprice,settlementtime,settlementtype," +
            "settlementuserid,settlementpoint,settlementamount,settlementpay," +
            "settlementpatytime from orderdetail o" +
            " inner join course c" +
            " inner join settlement s" +
            " on c.courseid = o.odcourseid" +
            " and o.odid = s.odid" +
            " where o.odcourseid = #{courseid}")
    public Page<SettlementDto> selectCourseDetail(String courseid,Page<SettlementDto> page);


    // 根据结算对象id和结算类型查询结算对象
    @Select("<script> "+
            "select" +
            "<choose>"+
            "<when test='settlementType == 2 || settlementType == 3'>" +
            " distinct u.username"+
            " from settlement s inner join sysuser u on s.settlementuserid = u.userid"+
            "</when>"+
            "<when test='settlementType == 1'>" +
            " distinct o.orgname"+
            " from settlement s inner join organization o on s.settlementuserid = o.orgid"+
            "</when>"+
            "</choose>"+
            "<where>"+
            "settlementuserid = #{settlementuserid}"+
            "</where>"+
            "</script>")
    public String getSettlementUsername(Integer settlementuserid,Integer settlementType);


    /**
     * @param page 封装分页参数
     * @param queryWrapper   封装查询条件
     * @return: com.etime.shycourse.dto.AccountUserDto
     * @description: 查询所有用户未支付的提成金额总和
     **/
    @Select("SELECT u.nickname,u.showpic,u.usertruename, s.settlementid, s.settlementuserid,u.userid,u.username,SUM(settlementamount) as countsum\n" +
            "FROM settlement s \n" +
            "join sysuser u \n" +
            "on s.settlementuserid=u.userid\n" +
            " ${ew.customSqlSegment} GROUP BY settlementuserid ")
    Page<AccountUserDto> selectAccountSumByPage(Page<AccountUserDto> page,
                                                @Param(Constants.WRAPPER) QueryWrapper<AccountUserDto> queryWrapper);


    /**
     * @param page  封装的分页参数
     * @param queryWrapper  条件构造器封装查询条件
     * @return: com.etime.shycourse.dto.AccountHistoryDto
     * @description:  根据时间段查询用户历史提成支付信息
     **/
    @Select("SELECT\n" +
            "s.settlementid,u.username,u.usertruename,u.showpic,\n" +
            "s.settlementpatytime,s.settlementuserid,\n" +
            "SUM(settlementsale) as settlementsaleSum,\n" +
            "SUM(settlementamount) as settlementamountSum\n" +
            "from\n" +
            "settlement s\n" +
            "join sysuser u\n" +
            "on s.settlementuserid = u.userid\n" +
            "${ew.customSqlSegment} group by settlementuserid " )
    Page<AccountHistoryDto> selectAccountHistory(Page<AccountHistoryDto> page,
                                                 @Param(Constants.WRAPPER)  QueryWrapper<AccountHistoryDto> queryWrapper);

    @Select("SELECT s.settlementuserid,\n" +
            "u.username,\n" +
            "s.settlementpatytime,\n" +
            "SUM(settlementsale) as settlementsaleSum,\n" +
            "SUM(settlementamount) as settlementamountSum\n" +
            "from\n" +
            "settlement s\n" +
            "join sysuser u\n" +
            "on s.settlementuserid = u.userid\n" +
            "${ew.customSqlSegment}")
    Page<AccountHistoryDto> selectAccountHistoryById(Page<AccountHistoryDto> page,
                                                     @Param(Constants.WRAPPER) QueryWrapper<AccountHistoryDto> queryWrapper);

    /**
     * @param page 封装的分页参数
     * @param queryWrapper  封装的查询条件
     * @return: com.etime.shycourse.dto.AccountMyDto
     * @description: 关联查询我的提成明细记录
     **/
    @Select("select u.showpic,u.usertruename,c.coursepic,o.odcoursename,s.settlementuserid,s.settlementpay,s.settlementsale,\n" +
            "s.settlementtype,s.settlementpoint,s.settlementamount,s.settlementtime,s.settlementpatytime\n" +
            "from orderdetail o join settlement s join course c join sysuser u on o.odid=s.odid and c.courseid = o.odcourseid and s.settlementuserid = u.userid " +
            "${ew.customSqlSegment}")
    Page<AccountMyDto> selectAccountMyByPage(Page<AccountMyDto> page,
                                             @Param(Constants.WRAPPER) QueryWrapper<AccountMyDto> queryWrapper);


    /*
    * 获取具有settlement的用户的总个数
    * settlementpay=1代表已经支付
    * settlementpay=0代表未支付
    * */
    @Select("select  count(DISTINCT u.userid) from sysuser u left join settlement s on u.userid=s.settlementuserid \n" +
            " where s.settlementid is not null and s.settlementpay=#{settlementpay}")
    Integer getCount(int settlementpay);

    /*
     * 根据userid查询该用户的settlement信息
     * settlementpay=0代表未支付
     * settlementpay=1代表已经支付
     * */
    @Select({"<script>" +
            "select * from settlement where settlementuserid=#{id} " +
            " <if test='settlementpay!=-1'> and settlementpay=#{settlementpay}</if>" +
            "</script>"})
    List<Settlement> getSettlementListByUserid(@Param("id") int id, @Param("settlementpay") int settlementpay );


    /*
     * 根据userid分页查询该用户信息settlement信息和订单明细信息
     * settlementpay代表settlement支付状态--1：已支付 0：未支付
     * id为用户id
     * */
    @Select("select s.*,u.* ,od.*,o.*,c.*  from sysuser u\n" +
            "left join settlement s\n" +
            "on u.userid = s.settlementuserid\n" +
            "left join orderdetail od\n" +
            "on s.odid=od.odid\n" +
            "left join orders o\n" +
            "on od.orderid=o.orderid\n" +
            "left join course c\n" +
            "on c.courseid= od.odcourseid \n" +
            "where  s.settlementid is not null\n" +
            "and u.userid=#{id}\n" +
            "and s.settlementpay=#{settlementpay}\n" +
            "limit #{pagenum},#{pagesize}")
    List<SettlementDto> getSettlementDetailsByUserId(@Param("id") int id,@Param("settlementpay") int settlementpay,@Param("pagenum") long pagenum,@Param("pagesize") long pagesize);




    /*
     * 根据userid不分页查询该用户信息settlement信息和订单明细信息
     * settlementpay代表settlement支付状态--1：已支付 0：未支付
     * id为用户id
     * */
    @Select("select s.*,u.* ,od.*,o.*,c.*  from sysuser u\n" +
            "left join settlement s\n" +
            "on u.userid = s.settlementuserid\n" +
            "left join orderdetail od\n" +
            "on s.odid=od.odid\n" +
            "left join orders o\n" +
            "on od.orderid=o.orderid\n" +
            "left join course c\n" +
            "on c.courseid= od.odcourseid \n" +
            "where  s.settlementid is not null\n" +
            "and u.userid=#{id}\n" +
            "and s.settlementpay=#{settlementpay}\n")
    List<SettlementDto> findSettlementDetailsByUserId(@Param("id") int id,@Param("settlementpay") int settlementpay);



    /*
     * 获取具有settlement的总数
     * settlementpay=1代表已经支付
     * settlementpay=0代表未支付
     * */
    @Select("select count( DISTINCT s.settlementid) from sysuser u\n" +
            "left join settlement s\n" +
            "on u.userid = s.settlementuserid\n" +
            "left join orderdetail od\n" +
            "on s.odid=od.odid\n" +
            "where  s.settlementid is not null\n" +
            "and u.userid=#{id}\n" +
            "and s.settlementpay=#{settlementpay}")
    int getSettlementCount(@Param("id") int id,@Param("settlementpay") int settlementpay);

    /**
     * @return: java.util.List<java.lang.String>
     * @description: 统计所有的支付时间
     **/
    @Select("select\n" +
            "settlementid,settlementpatytime\n" +
            "from settlement " +
            "where settlementpay=1 and settlementuserid=#{settlementuserid}")
    List<Settlement> selectPaytime(@Param("settlementuserid") Integer settlementuserid);

    /*
     * 支付提成订单
     * */
    @Update("update settlement set settlementpay=1,settlementpatytime=#{date} where settlementid =#{settlementid}   ")
    int updateSettlements(@Param("settlementid") int settlementid,@Param("date") String date);

    /**
     *  根据settlementid获取用户订单信息
     */
    @Select("select s.*,u.usertruename,o.orderusertruename,od.odcoursename from settlement s\n" +
            "left join orderdetail od on od.odid=s.odid\n" +
            "left join sysuser u on s.settlementuserid = u.userid\n" +
            "left join orders o on o.orderid=od.orderid\n" +
            "where settlementid=#{settlementid}")
    SettlementDto getSettlement(int settlementid);
    /*
            根据settlementid获取机构订单信息
         * */
    @Select("select s.*,org.orgname,orderusertruename,od.odcoursename from settlement s\n" +
            " left join orderdetail od on od.odid=s.odid\n" +
            " left join organization org  on s.settlementuserid = org.orgid\n" +
            " left join orders o on o.orderid=od.orderid\n" +
            " where settlementid=#{settlementid}")
    SettlementDto getOrgSettlement(int settlementid);



    /*
        获取所有settlement订单的时间
     * */

    @Select("select distinct settlementtime from settlement ${ew.customSqlSegment}")
    Page<Date>  getDateList(Page<Date> datePage, @Param(Constants.WRAPPER)QueryWrapper<Date> queryWrapper);



    /*
    * 机构查询符合条件的结算
    * */
    @Select("select *,sum(settlementsale) totalSale,sum(settlementamount) totalAmount,count(settlementid) count from settlement s\n" +
            "left join organization org on s.settlementuserid=org.orgid\n" +
            "${ew.customSqlSegment}\n"+
            "GROUP BY orgid")
    Page<SettlementDto> getOrgSettlements(Page<SettlementDto> page, @Param(Constants.WRAPPER)QueryWrapper<SettlementDto> queryWrapper);

    /*
     * 机构查询符合条件的结算明细
     * */
    @Select("select * from settlement s\n" +
            "left join organization org on s.settlementuserid=org.orgid\n" +
            "left join orderdetail od on od.odid=s.odid\n" +
            "left join course c on c.courseid=od.odcourseid\n" +
            "left join orders o on o.orderid=od.orderid\n"+
            "${ew.customSqlSegment}"
    )
    Page<SettlementDto> getOrgSettlementDetails(Page<SettlementDto> page, @Param(Constants.WRAPPER)QueryWrapper<SettlementDto> queryWrapper);
    /*
     用户
    * 根据日期和userid查询所有符合条件的settlement支付记录
    * */
    @Select("select * from settlement s\n" +
            "left join orderdetail od on od.odid=s.odid\n" +
            "left join orders o on o.orderid=od.orderid\n"+
            "left join sysuser u  on s.settlementuserid=u.userid\n"+
            "left join course c on od.odcourseid=c.courseid\n"+
            "${ew.customSqlSegment}"
    )
    List<SettlementDto>  getSettlementByDate( @Param(Constants.WRAPPER)QueryWrapper<SettlementDto> queryWrapper);

    /*
     机构
    * 根据日期和userid查询所有符合条件的settlement支付记录
    * */
    @Select("select * from settlement s\n" +
            "left join orderdetail od on od.odid=s.odid\n" +
            "left join orders o on o.orderid=od.orderid\n"+
            "left join organization org on s.settlementuserid=org.orgid\n"+
            "left join course c on od.odcourseid=c.courseid\n"+
            "${ew.customSqlSegment}"
    )
    List<SettlementDto>  orgGetSettlementByDate( @Param(Constants.WRAPPER)QueryWrapper<SettlementDto> queryWrapper);


    /**
     * @param queryWrapper 条件构造器
     * @return: com.etime.shycourse.dto.AccountUserDto
     * @description: 查询所有时间点对应的用户信息
     **/
    @Select("select \n" +
            "settlement.settlementid,sysuser.usertruename\n" +
            "from sysuser\n" +
            "join settlement\n" +
            "on sysuser.userid = settlement.settlementuserid ${ew.customSqlSegment}")
    List<AccountUserDto> selectUserInfo(
            @Param(Constants.WRAPPER) QueryWrapper<String> queryWrapper);

    /**
     * @param page 分页参数
     * @param queryWrapper 条件构造器
     * @return: com.baomidou.mybatisplus.extension.plugins.pagination.Page<com.etime.shycourse.dto.AccountMyDto>
     * @description: 查询历史提成详细信息
     **/
    @Select("select s.settlementid, u.showpic,u.usertruename,c.coursepic,o.odcoursename,s.settlementuserid,\n" +
            "s.settlementpay,s.settlementsale,s.settlementtype,\n" +
            "s.settlementpoint,s.settlementamount,s.settlementtime,s.settlementpatytime\n" +
            "from orderdetail o \n" +
            "join settlement s \n" +
            "join course c \n" +
            "join sysuser u \n" +
            "on o.odid=s.odid \n" +
            "and c.courseid = o.odcourseid \n" +
            "and s.settlementuserid = u.userid\n" +
            "${ew.customSqlSegment}")
    Page<AccountMyDto> selectHistroyDetails(Page<AccountMyDto> page,
                                            @Param(Constants.WRAPPER) QueryWrapper<AccountMyDto> queryWrapper);
    /*
         用户
     * 根据userid查询settlement支付日期列表
     * */
    @Select("select DISTINCT settlementtime from  settlement s\n" +
            "left join sysuser u  on s.settlementuserid=u.userid\n"+
            "${ew.customSqlSegment}"
    )
    Page<Date> findDateList(Page<Date> page,@Param(Constants.WRAPPER)QueryWrapper<Date> queryWrapper);

    /*
        机构
     * 根据userid查询settlement支付日期列表
     * */
    @Select("select DISTINCT settlementtime from  settlement s\n" +
            "left join organization org on s.settlementuserid=org.orgid\n" +
            "${ew.customSqlSegment}"
    )
    Page<Date> orgFindDateList(Page<Date> page,@Param(Constants.WRAPPER)QueryWrapper<Date> queryWrapper);


    /*
     * 查询所有用户结算信息
     *
     * */

    @Select(
            "select *,count(*) count ,sum(s.settlementsale) totalsale,sum(s.settlementamount) totalAmount from settlement s\n" +
                    "left join sysuser u on s.settlementuserid=u.userid\n" +
                    "${ew.customSqlSegment}"+
                    "GROUP BY settlementuserid"

    )
    Page<SettlementDto> getSettlements(Page<SettlementDto> page,@Param(Constants.WRAPPER)QueryWrapper<SettlementDto> queryWrapper);



}






